Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Hardware Enhancements

For a data warehouse, there are several hardware enhancements that can improve performance. These hardware enhancements can be beneficial in the area of CPU, I/O, and network, as described in the following sections.

CPU Enhancements

Enhancing the CPUs on your SMP or MPP system can provide instantaneous performance improvements, assuming that you are not I/O bound. The speed of CPUs is constantly being improved as are new and better cache designs.

For SMP or MPP machines, the process of enhancing the CPU may be as simple as adding an additional CPU board. Before you purchase an additional processor of the same type and speed, however, consider upgrading to a faster processor. For example, upgrading from a 66 MHz processor to a 133 MHz processor may provide more benefit than purchasing an additional 66 MHz CPU—with the added benefit that you now have the option of adding more 133 MHz CPUs. Because of the complexity and run time required by these queries, you can benefit from more and faster CPUs.

SMP and MPP computers provide scaleable CPU performance enhancements at a fraction of the cost of another computer. When upgrading your processors or adding additional processors, remember that your I/O and memory needs will probably increase along with the CPU performance. Be sure to budget for more memory and disk drives when you add processors.

I/O Enhancements

You can enhance I/O by adding disk drives or purchasing a hardware disk array. The data warehouse can benefit from the disk striping available in both hardware and software disk arrays. Using Oracle data file striping can also help the performance of your data warehouse.

If your system performs only one query at a time and you are not taking advantage of the Oracle Parallel Query option, you may not see a benefit from a hardware or software disk array. In this specific case, I do not recommend OS or hardware striping; you should use traditional Oracle striping. Because you are executing only one query at a time without using the Parallel Query option, the I/Os to the data files are purely sequential on the table scans. This scenario is somewhat rare; any variance from “pure table scans” results in degraded performance.

Hardware and software disk arrays have the added benefit of optional fault tolerance. As described in Chapter 15, “Disk Arrays,” each of the fault-tolerant RAID levels has its advantages and disadvantages. You should first choose the correct fault tolerance for your needs and then make sure that you have sufficient I/O capabilities to achieve the required performance level. If you use fault tolerance, you will most likely have to increase the number of disk drives in your system.


TIP:  Because data warehousing systems primarily involve a majority of read operations and fewer writes on some data, you can take advantage of the fault tolerance and read performance of RAID-5. Make sure that write activity is small and remember that there may be significant write activity in the temporary tables.

Another benefit of hardware disk arrays is caching. Most disk arrays on the market today offer some type of write or read/write cache on the controller. The effect of this cache is to improve the speed of writing to the disk; the cache also masks the overhead associated with fault tolerance. If your queries often perform table scans, you may see good improved performance with disk controllers that take advantage of read-ahead features.

Read-ahead occurs when the controller detects a sequential access and reads an entire track (or some other large amount of data) and caches the additional data in anticipation of a request from the OS. Unlike an OLTP system in which this is just wasted overhead, in the data warehouse where you are performing DSS queries, it is likely that you will need that data soon; if you do, it will be available very quickly.


CAUTION:  It is important to make sure that any controller you use with a write cache is protected against a power failure. Some disk array controllers on the market today (such as the Compaq disk array controllers) offer a battery-backed, mirrored memory cache that protects your data. Remember: Once Oracle believes that data has been written to the drive, that data had better be there.

Enhancements to the I/O subsystem almost always help in a data warehouse environment because large amounts of data are accessed. Be sure that you have a sufficient number of disk drives, properly configured. An I/O bottleneck is usually difficult to work around. As with all types of systems, a well-tuned application is very important.

Network Enhancements

Network performance may or may not be an issue in your data warehouse. Depending on the load generated during loading phases, you may see some degradation caused by a slow network. If this is the case, you can make several enhancements.

Segmenting the network can improve performance. This is particularly useful when data is loaded from several different machines. It may be necessary to reduce other traffic on the segments connecting the data warehouse to the data sources.

You may be able to take advantage of new networking hardware such as 100 megabit Ethernet and fiber-optic networks. When you increase the bandwidth of the network, you can transfer more data. Check your network periodically to see that you do not have excessive collisions or bandwidth problems. Any problems with the network can be easily solved by adding more or faster hardware.

Review of Enhancement Options

By taking advantage of some of the performance enhancement features available, you may see significant performance improvement. I have worked with the Oracle Parallel Query option and I am very impressed with the performance gains possible. Data warehousing systems are particularly well suited for the parallelizing of queries.

Using the Parallel Query option in conjunction with a disk array can be beneficial to your system’s performance. By combining these enhancements with other tuning options (such as a large block size and multiblock reads), you are on your way to having a well-tuned data warehouse.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.